package com.zwh.common.utils.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.zwh.modules.datamax.entity.DataMax;


/**
 * @Author monkey
 * @Date 2019/5/25 8:17
 **/
public class PoiUtil {


    /**
     * 将数据写入workbook返回
     * @param titles 标题数组
     * @param columns data数据的key列
     * @param sheetTitle 表名
     * @param datas 行数据列表
     * @return
     */
    public static Workbook exportExcel(String type, String headerTitle, String[] titles, String[] columns, String sheetTitle, List<Map<String, Object>> datas) {
        Workbook book=null;
        if(type.equals(".xls")){
             book = new HSSFWorkbook();
        }else{
             book = new XSSFWorkbook();
        }
        int rownum=0;

        Sheet sheet = book.createSheet(sheetTitle);
        sheet.setDefaultColumnWidth(13);

        if(StringUtils.isNotBlank(headerTitle)){
            CellStyle style = book.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            Font titleFont = book.createFont();
            titleFont.setFontName("Arial");
            titleFont.setFontHeightInPoints((short) 16);
            style.setFont(titleFont);


            Row titleRow = sheet.createRow(rownum++);
            titleRow.setHeightInPoints(30);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellStyle(style);
            titleCell.setCellValue(headerTitle);
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
                    titleRow.getRowNum(), titleRow.getRowNum(), titles.length-1));
        }

        CellStyle baseStyle = book.createCellStyle();
        baseStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        baseStyle.setBorderRight(BorderStyle.THIN);
        baseStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        baseStyle.setBorderLeft(BorderStyle.THIN);
        baseStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        baseStyle.setBorderTop(BorderStyle.THIN);
        baseStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        baseStyle.setBorderBottom(BorderStyle.THIN);
        baseStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = book.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        baseStyle.setFont(dataFont);

        CellStyle styleTitle = book.createCellStyle();
        styleTitle.cloneStyleFrom(baseStyle);
        styleTitle.setAlignment(HorizontalAlignment.CENTER);
        Font titleFont = book.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 11);
        titleFont.setBold(true);
        styleTitle.setFont(titleFont);

        //init column title
        Row firstRow = sheet.createRow(rownum++);
        if (titles != null) {
            int firstRowIndex = 0;
            for (String title : titles) {
                Cell cell = firstRow.createCell(firstRowIndex);
                cell.setCellStyle(styleTitle);
                cell.setCellValue(title);
                firstRowIndex++;
            }
        }

        if(datas==null||datas.size()==0){
            return book;
        }
        //从第三行开始写入数据
        int rowIndex = rownum;
        for (Map<String, Object> rowData : datas) {
            Row row = sheet.createRow(rowIndex);
            int col=0;
            for(String column:columns)
            {
                Cell cell = row.createCell(col);
                cell.setCellStyle(baseStyle);
                Object val = rowData.get(column);
                if(val instanceof String) {
                    cell.setCellValue((String) val);
                }else if(val instanceof Integer) {
                    cell.setCellValue((Integer) val);

                }else if(val instanceof Long) {
                    cell.setCellValue((Long) val);

                }else if(val instanceof Double) {
                    cell.setCellValue((Double) val);

                }else if(val instanceof Float) {
                    cell.setCellValue((Float) val);

                }else if(val instanceof Date) {
                    cell.setCellValue((Date) val);
//                    cellFormatString = "yyyy-MM-dd HH:mm";
                }else{
                    cell.setCellValue(val.toString());
                }
                col++;
            }
            rowIndex++;
        }
        return book;
    }

    public static void main(String[] args) {

        List<DataMax> dataMaxList = new ArrayList<>();
        DataMax dataMax = new DataMax();
        dataMax.setDataType("1");
        dataMax.setMaxSort(10);
        dataMaxList.add(dataMax);

        List<Map<String, Object>> exportList = JSONObject.parseObject(JSONObject.toJSONString(dataMaxList),
                new TypeReference<List<Map<String, Object>>>() {
                });


        String[] titles = {"类型", "最大排序号"};
        String[] columns = {"dataType", "maxSort"};
        try {
            Workbook workbook = PoiUtil.exportExcel(".xls", "数据导出", titles, columns, "数据导出", exportList);
            String filePath = PoiUtil.class.getResource("/").getPath() + "数据导出测试.xls";
            File file = new File(filePath);
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
            FileOutputStream outputStream = new FileOutputStream(file);
            // 解决中文乱码问题
            String fileName = URLEncoder.encode("数据导出测试.xlsx", "UTF-8");

            //写出内容
            workbook.write(outputStream);

            outputStream.flush();
            outputStream.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
